﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace DataShow.codeDAL
{
    public class SqlHelper
    {
        static string connStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.ToString();


        #region 执行ExecuteNonQuery
        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, params SqlParameter[] param)
        {
            using (SqlConnection con = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    return cmd.ExecuteNonQuery();

                }
            }
        } 
        #endregion

        #region 执行带参数的sql增删改语句
        /// <summary>
        /// 执行带参数的sql增删改语句
        /// </summary>
        /// <param name="sql">sql增删改语句</param>
        /// <param name="paras">参数集合</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, SqlParameter[] paras, CommandType ct)
        {
            int res;
            using (SqlConnection con = new SqlConnection(connStr))
            {

                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    con.Open();
                    cmd.Parameters.AddRange(paras);
                    cmd.CommandType = ct;
                    res = cmd.ExecuteNonQuery();
                }

            }
            return res;
        } 
        #endregion

        #region 获取sqldatareader
        /// <summary>
        /// 获取sqldatareader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public SqlDataReader GetReader(string sql, params SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connStr);

            SqlCommand cmd = new SqlCommand(sql, conn);

            conn.Open();
            cmd.Parameters.AddRange(param);
            conn.Close();
            return cmd.ExecuteReader();


        } 
        #endregion

        #region 获取DataTable
        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql">执行的sql</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, conn))
                {
                    if (param != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(param);
                    }
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    return dt;
                }
            }
        } 
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    conn.Open();
                    return cmd.ExecuteScalar();
                }
            }
        } 
        #endregion

        #region 该方法执行传入的SQL查询语句
        /// <summary>
        /// 该方法执行传入的SQL查询语句
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sql, CommandType ct)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {

                    DataTable dt = new DataTable();
                    cmd.CommandType = ct;
                    conn.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        dt.Load(sdr);
                    }
                    conn.Close();
                    return dt;
                }
            }

        } 
        #endregion

        #region 执行带参数的SQL查询语句
        /// <summary>
        /// 执行带参数的SQL查询语句
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="?">参数集合</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sql, SqlParameter[] paras, CommandType ct)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    DataTable dt = new DataTable();
                    cmd.Parameters.AddRange(paras);
                    conn.Open();
                    cmd.CommandType = ct; 
                    using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        dt.Load(sdr);
                    }
                    conn.Close();
                    return dt;
                }
            }

        } 
        #endregion


        #region 返回执行的sql语句的第一行第一列的值


        /// <summary>
        /// 返回执行的sql语句的第一行第一列的值
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public string ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    try
                    {

                        conn.Open();
                        object obj = cmd.ExecuteScalar();
                        if (obj != null)
                        {
                            return obj.ToString();
                        }
                        return "";
                    }

                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open)
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        #endregion

    }
}
